<?php

namespace App\Db;

use Illuminate\Support\Facades\DB;

class Dashboard
{
    public const EVERYDAY_WORD = 'everyday_word';

    public function saveEverydayWord($text, $date = false)
    {
        if (!$date) {
            $date = date('Y-m-d');
        }
        return DB::table(self::EVERYDAY_WORD)->insert([
            'text' => $text,
            'create_date' => $date
        ]);
    }

    public function getEverydayWord($date = false)
    {
        if (!$date) {
            $date = date('Y-m-d');
        }
        $dbRes = DB::table(self::EVERYDAY_WORD)->where(['create_date' => $date])->first();
        return $dbRes ?? false;
    }

    public function articleStatistic($siteId)
    {
        // 总文章 本月文章 今年文章
        $articleTotal = DB::table('blog')->where([
            'site' => $siteId,
            'is_del' => 0
        ])->count();
        $articleMonth = DB::table('blog')->where([
            'site' => $siteId,
            'is_del' => 0
        ])->whereRaw("create_time like '". date('Y-m') ."-%'")->count();
        $articleYear = DB::table('blog')->where([
            'site' => $siteId,
            'is_del' => 0
        ])->whereRaw("create_time like '". date('Y') ."-%'")->count();

        return [
            'articleTotal' => $articleTotal,
            'articleMonth' => $articleMonth,
            'articleYear' => $articleYear
        ];
    }

    public function visitStatistic($siteId)
    {
        // 总访问量 今日访问量 本月访问量 今年访问量
        $totalVisit = DB::table('visitlog')->where([
            'site' => $siteId
        ])->whereRaw("instr(path, 'admin') = 0")->count();
        $todayVisit = DB::table('visitlog')->where([
            'site' => $siteId
        ])->whereRaw("instr(path, 'admin') = 0")->whereBetween('create_time', [date('Y-m-d') . " 00:00:00", date('Y-m-d') . " 23:59:59"])->count();
        $thisMonthVisit = DB::table('visitlog')->where([
            'site' => $siteId
        ])->whereRaw("instr(path, 'admin') = 0")->where('create_time', 'like', date('Y-m') . '-%')->count();
        $thisYearVisit = DB::table('visitlog')->where([
            'site' => $siteId
        ])->where('create_time', 'like', date('Y') . '-%')->whereRaw("instr(path, 'admin') = 0")->count();
        // 今年访问量
        $yearHeatGrap = DB::select("
            select
                strftime('%Y-%m-%d', create_time) as create_date, count(id) as c
            from
                hikari2_visitlog
            where
                create_time like '". date('Y') ."-%' and
                site = ". $siteId ." and
                instr(path, 'admin') = 0
            GROUP BY create_date
        ;");
        // 最忠实的ip top10
        $ipTop10 = DB::select("
            select
                ip, count(id) as c
            from
                hikari2_visitlog
            where
                site = ". $siteId ." and
                instr(path, 'admin') = 0
            GROUP BY ip
            order by c desc
            limit 10
        ;");
        return [
            'totalVisit' => $totalVisit,
            'todayVisit' => $todayVisit,
            'thisMonthVisit' => $thisMonthVisit,
            'thisYearVisit' => $thisYearVisit,
            'yearHeatGrap' => $yearHeatGrap,
            'ipTop10' => $ipTop10
        ];
    }
}
